# -*- coding: utf-8 -*-
import re
import sqlite3
import urllib.request

import xlwt
from bs4 import BeautifulSoup

"""
    豆瓣爬虫：
    作业：爬取豆瓣电影Top250，并保存只 Excel 或 sqlite 数据库
    1. 获取数据
        * urllib
        * 访问权限: cookie
    2. 数据清洗
        * re
        * bs4
        * 格式化
        * 特殊数据处理：如inq、ename等
    3. 保存数据
        * xlwt
        * sqlite3
"""

# 影片详情链接
findLink = re.compile(r'<a href="(.*?)">')
# 影片图片链接
findImgSrc = re.compile(r'<img .* src="(.*?)"', re.S)  # .S 忽略匹配内容中的换行符
# 影片名称
findTitle = re.compile(r'<span class="title">(.*)</span>')
# 影片评分
findRating = re.compile(r'<span class="rating_num" property="v:average">(.*)</span>')
# 评价人数
findComment = re.compile(r'<span>(\d*)人评价</span>')
# 影片简介
findInq = re.compile(r'<span class="inq">(.*)</span>')
# 影片相关内容
findBd = re.compile(r'<p class="">(.*?)</p>', re.S)

"""
单个影片详情——结构分析：
<div class="item">
<div class="pic">
<em class="">1</em>
<a href="https://movie.douban.com/subject/1292052/">
<img alt="肖申克的救赎" class="" src="https://img2.doubanio.com/view/photo/s_ratio_poster/public/p480747492.jpg" width="100"/>
</a>
</div>
<div class="info">
<div class="hd">
<a class="" href="https://movie.douban.com/subject/1292052/">
<span class="title">肖申克的救赎</span>
<span class="title"> / The Shawshank Redemption</span>
<span class="other"> / 月黑高飞(港)  /  刺激1995(台)</span>
</a>
<span class="playable">[可播放]</span>
</div>
<div class="bd">
<p class="">
                            导演: 弗兰克·德拉邦特 Frank Darabont   主演: 蒂姆·罗宾斯 Tim Robbins /...<br/>
                            1994 / 美国 / 犯罪 剧情
                        </p>
<div class="star">
<span class="rating5-t"></span>
<span class="rating_num" property="v:average">9.7</span>
<span content="10.0" property="v:best"></span>
<span>2464533人评价</span>
</div>
<p class="quote">
<span class="inq">希望让人自由。</span>
</p>
<p>
<span class="gact">
<a class="j a_collect_btn" href="https://movie.douban.com/wish/201840134/update?add=1292052" name="sbtn-1292052-wish" rel="nofollow" target="_blank">想看</a>
</span>  
        
        <span class="gact">
<a class="j a_collect_btn" href="https://movie.douban.com/collection/201840134/update?add=1292052" name="sbtn-1292052-collection" rel="nofollow" target="_blank">看过</a>
</span>  
    </p>
</div>
</div>
</div>
"""


# 抓取网页
def getData(baseurl):
    datalist = []
    for i in range(0, 10):
        url = baseurl + str(i * 25)
        html = askURL(url)  # 保存获取到的网页源码

        # 逐一解析数据
        soup = BeautifulSoup(html, 'lxml')
        for item in soup.find_all("div", class_="item"):
            # print(item)  # 测试：产看电影item全部信息
            data = []  # 保存一部电影的全部信息
            item = str(item)

            link = re.findall(findLink, item)[0]  # 通过正则查找指定字符串
            data.append(link)

            imgSrc = re.findall(findImgSrc, item)[0]
            data.append(imgSrc)

            titles = re.findall(findTitle, item)
            if len(titles) == 2:
                ctitle = titles[0]
                data.append(ctitle)  # 添加中文名
                otitle = titles[1].replace("/", "")  # 去掉无关的字符
                otitle = " ".join(otitle.split())  # 替换特殊字符 \xa0——不间断空白符
                data.append(otitle)  # 添加外国名
            else:
                data.append(titles[0])
                data.append(' ')  # 如果不存在外国名，该位置留空

            rating = re.findall(findRating, item)[0]
            data.append(rating)  # 添加评分

            counts = re.findall(findComment, item)[0]
            data.append(counts)  # 添加评价数

            try:
                inq = re.findall(findInq, item)[0]  # 使用try过滤一些不存在inq的项目(返回值为空)，不然在取值时会报错"list out of index"
            except IndexError:
                inq = []
                print(ctitle + " No inq！")
            # print(inq)  # 测试概况信息
            if len(inq) != 0:
                inq = inq.replace("。", "")  # 去掉句号
                data.append(inq)  # 添加概况
            else:
                data.append(" ")  # 如果不存在，留空

            bd = re.findall(findBd, item)[0]
            bd = re.sub(r'<br(\s+)?/>', " ", bd)  # 替换 <br/>
            bd = re.sub(r'/', " ", bd)  # 替换 /
            bd = " ".join(bd.split())  # 替换特殊字符 \xa0——不间断空白符
            data.append(bd.strip())  # 去掉空格
            datalist.append(data)
    # print(datalist[0])
    # print(datalist[1])
    return datalist


# 获取指定url的网页内容
def askURL(url):
    head = {
        "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko)"
                      "Chrome/93.0.4577.82 Safari/537.36",
        "cookie": 'bid=hSkzoZlpyvc; _vwo_uuid_v2=D1FC2A04537A3C7F6D9A97EA63CF9D957|966dfc0b794f31b57422d74e7b969a91; '
                  'll="108288"; __utmv=30149280.20184; _ga=GA1.2.625433070.1605677811; '
                  'gr_user_id=7f136d8a-d29c-478c-bd2e-daff1224e5d2; __utmc=30149280; '
                  'viewed="35538455_34895031_10429842_35094680_25887672_25707025_30362255_26895401_26935523_26259626"; '
                  '__utmc=223695111; dbcl2="201840134:c69tEZ3wiMc"; '
                  'ck=MHNj; push_doumail_num=0; ct=y; push_noty_num=0; '
                  '__gads=ID=49d736a6417a89d3-22bd416c5ccc0035:T=1624705091:RT=1633748767:'
                  'S=ALNI_Ma2gN8Uwo-QP1Szd--7SkplI_iabA; _gid=GA1.2.964386817.1634020594; ap_v=0,6.0;'
                  ' _pk_ref.100001.4cf6=%5B%22%22%2C%22%22%2C1634020597%2C%22https%3A%2F%2Fwww.google.com.hk%2F%22%5D; '
                  '_pk_ses.100001.4cf6=*; __utma=30149280.625433070.1605677811.1633935698.1634020597.45; '
                  '__utmb=30149280.0.10.1634020597; '
                  '__utmz=30149280.1634020597.45.36.utmcsr=google|utmccn=(organic)|'
                  'utmcmd=organic|utmctr=(not%20provided); '
                  '__utma=223695111.625433070.1605677811.1632738485.1634020597.4; __utmb=223695111.0.10.1634020597; '
                  '__utmz=223695111.1634020597.4.2.utmcsr=google|utmccn=(organic)|'
                  'utmcmd=organic|utmctr=(not%20provided); '
                  '_pk_id.100001.4cf6=c5c34b194ab284a6.1631780875.4.1634020672.1632738501.'

    }
    request = urllib.request.Request(url, headers=head)
    html = ""
    try:
        response = urllib.request.urlopen(request)
        html = response.read().decode("utf-8")
        # print(html)  # debug
    except urllib.error.URLError as e:
        if hasattr(e, "code"):
            print(e.code)

        if hasattr(e, "reason"):
            print(e.reason)

    return html


def saveData2Excel(savepath, datalist):
    print("save...")
    length = len(datalist)  # datalist数据条数
    book = xlwt.Workbook(encoding="utf-8")  # 创建 workbook 对象
    sheet = book.add_sheet("豆瓣电影250", cell_overwrite_ok=True)  # 创建工作表
    col = ("电影详情链接", "图片链接", "影片中文名", "影片外文名", "评分", "评论数", "概况", "相关信息")
    for i in range(0, 8):
        sheet.write(0, i, col[i])  # 列名

    for i in range(0, length):
        print("第%d条" % (i + 1))
        data = datalist[i]
        for j in range(0, 8):
            sheet.write(i + 1, j, data)

    book.save(savepath)  # 保存数据


def saveData2DB(dbpath, datalist):
    init_db(dbpath)
    conn = sqlite3.Connection(dbpath)
    print("Opened database successfully!")
    cursor = conn.cursor()

    # 存数据
    count = 0
    for data in datalist:
        for index in range(len(data)):
            if index == 4 or index == 5:
                continue
            data[index] = '"' + data[index] + '"'  # 格式化内容

        sql = '''
                insert into movies (movie, img, cname, ename, score, rated, inq, bd)
                values (%s)''' % ",".join(data)
        # print(sql)
        cursor.execute(sql)
        conn.commit()
        count += 1
        print("抓取成功：%d，电影名称：%s" % (count, data[2]))

    cursor.close()
    conn.close()


def init_db(dbpath):
    conn = sqlite3.Connection(dbpath)
    cursor = conn.cursor()  # 获取游标
    # 建表：
    sql = '''
            create table movies (
                id integer primary key autoincrement,
                movie message_text ,
                img message_text ,
                cname varchar ,
                ename varchar ,
                score numeric ,
                rated numeric ,
                inq message_text ,
                bd message_text 
            );
        '''
    cursor.execute(sql)

    conn.commit()
    cursor.close()
    conn.close()


if __name__ == '__main__':
    baseurl = "https://movie.douban.com/top250?start="
    savepath = "movie.xls"
    dbpath = "movie.db"
    # askURL("https://movie.douban.com/top250?start=0")
    datalist = getData(baseurl)
    saveData2Excel(savepath, datalist)
    saveData2DB(dbpath, datalist)

    print("爬取完毕！")
